
CREATE DATABASE IF NOT EXISTS `allyfolio` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `allyfolio`;

DELIMITER $$
--
-- Procedures
--
DROP PROCEDURE IF EXISTS `getDefaultPrivacySettingForUser`$$
CREATE  PROCEDURE `getDefaultPrivacySettingForUser`(IN `userid` INT(11) ZEROFILL, OUT `privacytype` INT(11) ZEROFILL)
    READS SQL DATA
    COMMENT 'Procedure to get user default privacy setting regarding feed etc'
SELECT 
	Default_Setting INTO privacytype 
FROM 
	af_user_privacy_settings 
WHERE User_ID=userid$$

DROP PROCEDURE IF EXISTS `getOriginUserIDForPostActivity`$$
CREATE  PROCEDURE `getOriginUserIDForPostActivity`(IN `recordid` INT(11) ZEROFILL, IN `activitytablename` VARCHAR(100), IN `returnfield` VARCHAR(100), IN `pkeyfield` VARCHAR(100), OUT `owneruserid` INT(11) ZEROFILL)
    READS SQL DATA
    COMMENT 'Procedure To GET Owner ID of the activity whose post is going to like'
SELECT 
	User_ID INTO owneruserid 
FROM 
	af_user_posts_activities 
WHERE POST_ID=recordid$$

DROP PROCEDURE IF EXISTS `getTotalLikeCountsForComments`$$
CREATE  PROCEDURE `getTotalLikeCountsForComments`(IN `postid` INT(11), IN `commentid` INT(11), OUT `ucounts` INT(11))
    NO SQL
    COMMENT 'to fetch total likes for comments'
select IFNULL(count(Likes_ID),0) as LikeCount into ucounts from af_user_post_comments_likes where Post_ID=postid and Comment_ID=commentid$$

DROP PROCEDURE IF EXISTS `getTotalLikeCountsForFeeds`$$
CREATE  PROCEDURE `getTotalLikeCountsForFeeds`(IN `postid` INT(11), OUT `ucounts` INT(11))
    READS SQL DATA
    COMMENT 'to fetch total likes for post'
select IFNULL(count(Likes_ID),0) as LikeCount into ucounts from af_user_post_likes where Post_ID=postid$$

DROP PROCEDURE IF EXISTS `getTotalShareCountsForFeeds`$$
CREATE  PROCEDURE `getTotalShareCountsForFeeds`(IN `postid` INT(11), OUT `ucounts` INT(11))
    READS SQL DATA
    COMMENT 'to fetch total shares for post'
select IFNULL(count(Share_ID),0) as ShareCount into ucounts from af_user_post_shares where Post_ID=postid$$

DROP PROCEDURE IF EXISTS `getUniqueFeedIDToNotify`$$
CREATE  PROCEDURE `getUniqueFeedIDToNotify`(IN `lastNotificationRead` INT(11) ZEROFILL, IN `userid` INT(11) ZEROFILL)
    READS SQL DATA
    COMMENT 'To fetch only those notifications meant for me'
select distinct(Source_Record_ID) from af_notifications where Status = 'Active' and Notification_ID > lastNotificationRead
and 
( 
    CASE 
        WHEN Privacy_Type_ID =1 THEN 
                (
                SELECT count(user_id) 
                FROM `af_users` 
                WHERE 
                    user_id=Record_Owner_ID and 
                    FIND_IN_SET(Userid,`Active_Friends_IDs` ) and 
                    not FIND_IN_SET(Userid,`Blocked_Friends_IDs` )
                ) = 1 
                
                OR 
                
                (
                select count(Follower_ID) 
                from af_source_followers 
                where 
                    User_ID=Userid and 
                    source_id=Record_Owner_ID and 
                    source_type='PROFILE' 
                ) = 1 
                
                OR 
                
                (
                select count(Post_ID) 
                from af_user_posts_activities 
                where 
                    post_id=record_id and 
                    Friend_ids in (select Active_Friends_IDs from af_users where user_id=Userid)
                ) = 1 
                
        WHEN Privacy_Type_ID =2 THEN 
            (
            SELECT count(user_id) 
            FROM `af_users` 
            WHERE user_id=Record_Owner_ID and 
            FIND_IN_SET(Userid,`Active_Friends_IDs` ) and 
            not FIND_IN_SET(Userid,`Blocked_Friends_IDs` )
            ) = 1 
            
        WHEN Privacy_Type_ID =3 THEN 
            (
            select count(Post_ID) 
            from af_user_posts_activities 
            where 
                post_id=record_id and 
                Friend_ids in (select Active_Friends_IDs from af_users where user_id=Userid)
            ) = 1 
            
        WHEN Privacy_Type_ID =4 THEN 
            (
            select count(Friend_ID) 
            from af_cluster_friends 
            where Cluster_ID in (Select Cluster_ID from af_user_posts_activities where post_id=record_id ) and 
            status=1 and 
            friend_id=Userid
            ) = 1 
            
    ELSE 1 

END 

)
order by Notification_ID desc$$

DROP PROCEDURE IF EXISTS `getUserDetails`$$
CREATE  PROCEDURE `getUserDetails`(IN `uid` INT(11))
    READS SQL DATA
    COMMENT 'used to fetch user details'
SELECT  `User_ID` AS uid,  `Allyfolio_User_ID` AS plink, IF(LENGTH(Profile_Pic_Path)>0,profile_pic_path,'images/default_user_pic.jpg') AS picpath,  `Full_Name` AS uname,  `Is_Celebrity` 
FROM  `af_users` 
WHERE  `User_ID` =uid$$

DROP PROCEDURE IF EXISTS `getWithoutFeedNotifications`$$
CREATE  PROCEDURE `getWithoutFeedNotifications`(IN `lastNotificationRead` INT(11), IN `Userid` INT(11))
    READS SQL DATA
    COMMENT 'use to fetch all notifications'
select * from af_notifications where Status = 'Active' and Notification_ID > lastNotificationRead
and 
( 
    CASE 
        WHEN Module='User' THEN
    		Action_Type='Friend_Request' and FIND_IN_SET(4,`Friend_IDs` )>0
    ELSE 1 

END 

)
and Module<>'NewsFeeds'
order by Notification_ID desc$$

DELIMITER ;